﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
namespace ExcelAddIn2010
{
    public static class WorksheetOp
    {
        public static void DeleteBlankRows(Excel.Worksheet wksht)
        {
            gValue.ExcelApp.ScreenUpdating = false;
            int nStart = wksht.UsedRange.Cells[1,1].Row + wksht.UsedRange.Rows.Count - 1;

            for (int nRow = nStart; nRow > 0; nRow--)
            {
                if (0 == gValue.ExcelApp.WorksheetFunction.CountA(wksht.Rows[nRow]))
                {
                    wksht.Rows[nRow].Delete();
                }
            }
            gValue.ExcelApp.ScreenUpdating = true;
        }
        public static void DeleteAllHyperlinks(Worksheet wkSht)
        {
            wkSht.Hyperlinks.Delete();
        }
        public static void DeleteAllHyperlinks()
        {
            Worksheet wkSht = gValue.ExcelApp.ActiveSheet;
            wkSht.Hyperlinks.Delete();
            wkSht = null;
        }
        public static void DeleteAllComments(Worksheet wkSht)
        {
            wkSht.UsedRange.ClearComments();
        }
        public static void DeleteAllComments()
        {
            Worksheet wkSht = gValue.ExcelApp.ActiveSheet;
            wkSht.UsedRange.ClearComments();
            wkSht = null;
        }
        /// <summary>
        /// 移除工作表保护密码
        /// </summary>
        /// <param name="wkSht"></param>
        public static void RemoveWkshtPassword(Worksheet wkSht)
        {
            if (!wkSht.ProtectContents)
            {
                return;
            }
            int FixLen = 11;
            Char[] cBase = { 'A', 'B' };
            Char[] cPassword = new Char[FixLen];
            string strPassword = "";
            for (int idx = 0; idx < Math.Pow(2, FixLen); idx++)
            {
                for (int i = cPassword.GetLowerBound(0); i <= cPassword.GetUpperBound(0); i++)
                {
                    cPassword[cPassword.GetUpperBound(0) - i] = cBase[(idx >> i) & 1];
                }
                for (int j = 32; j < 127; j++)
                {
                    strPassword = string.Join("", cPassword) + Convert.ToChar(j);
                    try
                    {
                        wkSht.Unprotect(strPassword);
                    }
                    catch (System.Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    if (!wkSht.ProtectContents)
                    {
                        return;
                    }
                }
            }
        }
    }

}
